﻿using MLSMooc.BO;
using MLSMooc.DAO.Interface.Project;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using MLSMooc.Helper;
using Dapper;


namespace MLSMooc.DAO.Implement.Project
{
    public class ProjectDAO : IProjectDAO
    {
        private static ProjectDAO instance = new ProjectDAO();
        private ProjectDAO() {}

        public static ProjectDAO Getinstance() => instance;

        private static readonly string queryField = "select p.*,t.Id as Tid,t.Section,t.Company,t.Position,t.TName  from Mooc_Project p join Mooc_Teach t on p.Pro_Lecturer = t.Id where p.isDelete=0";

        public async Task<ProjectBO> GetByIdAsync(int id)
        {
            using (SqlConnection conn=new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder(queryField);
                sql.Append(" and p.Id=@id ");
                var res = await conn.QueryAsync<ProjectBO>(sql.ToString(),new { id=id});
                return res.FirstOrDefault();
            }
        }

        public async Task<IEnumerable<ProjectBO>> GetListAsync()
        {
            using (SqlConnection conn=new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder(queryField);
                var res = await conn.QueryAsync<ProjectBO>(sql.ToString());
                return res;
            }
        
        }

        public async Task<IEnumerable<ProjectBO>> GetListVideoAsync()
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder(queryField);
                sql.Append($@" and p.Pro_VideoUrl<>''");
                var res = await conn.QueryAsync<ProjectBO>(sql.ToString());
                return res;
            }

        }

        public async Task<IEnumerable<TeachBO>> GetTeachAsync(string id)
        {
            using (SqlConnection conn=new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder("select * from Mooc_Teach where isDelete=0 and Id in("+id+")");
                var res = await conn.QueryAsync<TeachBO>(sql.ToString());
                return res;
            }
        }
    }
}
